SemiVariance
Updated 2023-10-09 21:03:26.143000
Syntax
SELECT [westclintech].[wct].[SemiVariance](
<@R, float,>)
Description
Use the aggregate function SemiVariance to calculate the semi-variance of asset returns. The formula for SemiVariance is:
Where
{"columns":[{"field":"column 1"},{"field":"column 2"},{"field":"column 3"}],"rows":[{"column 1":"R","column 2":"=","column 3":"asset return"},{"column 1":"R","column 2":"=","column 3":"average asset return"},{"column 1":"n","column 2":"=","column 3":"number of rows where R < R"}]}
Arguments
@R
the asset return for a period; the percentage return in floating point format (i.e. 10% = 0.10). @R is an expression of type float or of a type that can be implicitly converted to float.
Return Type
float
Remarks
If @R IS NULL it is not included in the calculation.
If there are no non-NULL rows in a GROUP then NULL is returned.
Examples
In this example we have returns for an asset and its benchmark for the last three years.
SELECT wct.SemiVariance(Ra) as SemiVariance
FROM
(
VALUES
('2012-12-31', 0.001378, -0.003929),
('2013-01-31', 0.028677, -0.001701),
('2013-02-28', 0.005801, 0.003165),
('2013-03-31', 0.01442, -0.006487),
('2013-04-30', 0.00229, -0.004653),
('2013-05-31', 0.014905, 0.009577),
('2013-06-30', 0.008594, 0.00588),
('2013-07-31', 0.011531, 0.005089),
('2013-08-31', 0.008268, 0.005233),
('2013-09-30', 0.013993, -0.004338),
('2013-10-31', 0.009147, -0.006109),
('2013-11-30', -0.00316, -0.002222),
('2013-12-31', -0.00595, 0.005451),
('2014-01-31', 0.013398, -0.008099),
('2014-02-28', 0.002847, -0.000299),
('2014-03-31', -0.009544, -0.009809),
('2014-04-30', 0.002516, 0.008875),
('2014-05-31', 0.004626, -0.002681),
('2014-06-30', -0.002141, 0.000312),
('2014-07-31', 0.009247, 0.00936),
('2014-08-31', -0.01253, 0.005434),
('2014-09-30', 0.00441, 0.008157),
('2014-10-31', 0.01626, -0.006766),
('2014-11-30', 0.013207, 0.005742),
('2014-12-31', -0.008561, -0.005063),
('2015-01-31', 0.012357, 0.004357),
('2015-02-28', -0.002057, -0.00044),
('2015-03-31', 0.008217, -0.004866),
('2015-04-30', -0.013439, -0.007649),
('2015-05-31', 0.004391, -0.002073),
('2015-06-30', -0.008997, 0.00399),
('2015-07-31', -0.001878, -0.00613),
('2015-08-31', -0.014381, -0.00351),
('2015-09-30', -0.001885, 0.001172),
('2015-10-31', -0.001121, -0.000024),
('2015-11-30', 0.000816, 0.002596),
('2015-12-31', -0.003088, -0.009025)
) n (dt, Ra, Rb);
This produces the following result.
{"columns":[{"field":"SemiVariance","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"SemiVariance":"0.00922003188116091"}]}
In this example we have 6 managers plus a benchmark stored in the #managers table which is in 'spreadsheet' format.
SELECT *
INTO #managers
FROM ( VALUES ('2012-12-31', -0.002546, NULL, NULL, 0.003006, NULL, 0.005007, -0.001839),
('2013-01-31', 0.00977, NULL, NULL, 0.001, NULL, -0.007579, 0.008614),
('2013-02-28', 0.024726, NULL, NULL, 0.005703, NULL, 0.006496, -0.004624),
('2013-03-31', 0.000942, NULL, NULL, 0.007578, NULL, 0.008333, 0.005031),
('2013-04-30', 0.022139, NULL, NULL, 0.007118, NULL, -0.007312, -0.005431),
('2013-05-31', 0.019449, NULL, NULL, 0.005659, NULL, -0.003902, -0.001878),
('2013-06-30', -0.007964, NULL, NULL, 0.003757, NULL, -0.001899, -0.00706),
('2013-07-31', -0.008262, -0.002824, NULL, 0.003762, NULL, -0.007347, -0.001982),
('2013-08-31', 0.009617, 0.007319, NULL, 0.0022, NULL, -0.002116, 0.003867),
('2013-09-30', -0.004118, 0.004128, NULL, 0.001394, NULL, 0.008333, 0.001356),
('2013-10-31', 0.010754, -0.001578, NULL, 0.003483, NULL, -0.004724, -0.001342),
('2013-11-30', 0.002402, 0.020835, NULL, 0.001, NULL, 0.003954, -0.000306),
('2013-12-31', 0.004581, 0.015689, NULL, 0.002146, NULL, 0.008333, 0.001118),
('2014-01-31', -0.00055, -0.001027, -0.008245, 0.00732, NULL, 0.004025, -0.007227),
('2014-02-28', -0.001512, 0.001653, -0.009029, 0.009919, NULL, -0.008333, -0.003878),
('2014-03-31', 0.008784, 0.004364, -0.011608, 0.001, NULL, -0.008333, -0.004822),
('2014-04-30', 0.008412, -0.012369, -0.004692, 0.004536, NULL, -0.006303, 0.004306),
('2014-05-31', 0.003945, 0.010651, -0.016833, 0.001, NULL, -0.007974, 0.005221),
('2014-06-30', 0.012371, 0.01773, -0.010384, 0.010593, NULL, -0.004781, -0.000731),
('2014-07-31', 0.011915, 0.004308, -0.012965, 0.001, NULL, 0.007751, -0.009239),
('2014-08-31', -0.013738, 0.00039, 0.000009, 0.005139, 0.001, -0.003319, -0.003636),
('2014-09-30', -0.004081, 0.01968, -0.008214, 0.007976, 0.001, -0.003799, 0.005563),
('2014-10-31', 0.01608, 0.015291, -0.002969, 0.01303, 0.001, -0.004645, 0.001599),
('2014-11-30', 0.011241, 0.012312, 0.007088, 0.00384, 0.000852, 0.006783, -0.008487),
('2014-12-31', -0.004251, 0.008737, -0.013576, 0.001, 0.001, -0.0034, 0.001013),
('2015-01-31', -0.004039, 0.012938, -0.011891, 0.004576, 0.001, -0.004195, -0.002653),
('2015-02-28', 0.026326, 0.019695, -0.013178, 0.010737, 0.001, 0.006418, -0.00952),
('2015-03-31', -0.000628, 0.008029, 0.005917, 0.017461, 0.00048, -0.00501, 0.007879),
('2015-04-30', 0.005688, 0.01249, -0.018813, 0.001, -0.002104, 0.003657, 0.009886),
('2015-05-31', 0.010039, 0.008442, -0.01465, 0.001, 0.001, 0.001024, 0.001695),
('2015-06-30', -0.004267, 0.001391, -0.022504, 0.003104, 0.001, -0.003507, 0.009854),
('2015-07-31', -0.001672, 0.020558, -0.011859, 0.001, 0.001, NULL, 0.008361),
('2015-08-31', 0.015726, 0.017419, -0.004556, 0.001, -0.000462, NULL, -0.007253),
('2015-09-30', 0.001977, 0.007301, -0.00811, 0.013011, -0.001032, NULL, 0.009911),
('2015-10-31', 0.016201, 0.00877, -0.005536, 0.014527, -0.000833, NULL, 0.009897),
('2015-11-30', 0.019996, -0.000785, -0.002161, 0.001947, 0.001, NULL, -0.007307),
('2015-12-31', -0.003254, 0.009595, -0.013785, 0.001, 0.001, NULL, 0.007196)) n (dt, man1, man2,
man3, man4, man5,
man6, bmark);
To calculate the semi-variance for each manager against the benchmark we run the following SQL.
SELECT wct.SemiVariance(man1) as man1,
wct.SemiVariance(man2) as man2,
wct.SemiVariance(man3) as man3,
wct.SemiVariance(man4) as man4,
wct.SemiVariance(man5) as man5,
wct.SemiVariance(man6) as man6,
wct.SemiVariance(bmark) as bmark
FROM #managers;
This produces the following result.
{"columns":[{"field":"man1","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"man2","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"man3","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"man4","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"man5","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"man6","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"},{"field":"bmark","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"man1":"0.00905822245566325","man2":"0.00860693161378923","man3":"0.00615765424242306","man4":"0.00314360617310065","man5":"0.00168685674783494","man6":"0.0047286238279909","bmark":"0.00582466662913409"}]}
Using the same data from the previous example in the #nmanagers table which is in 3rd normal form we perform the same calculation.
SELECT *
INTO #nmanagers
FROM
(
VALUES
('2012-12-31', 'man1', -0.002546),
('2012-12-31', 'man4', 0.003006),
('2012-12-31', 'man6', 0.005007),
('2012-12-31', 'bmark', -0.001839),
('2013-01-31', 'man1', 0.009770),
('2013-01-31', 'man4', 0.001000),
('2013-01-31', 'man6', -0.007579),
('2013-01-31', 'bmark', 0.008614),
('2013-02-28', 'man1', 0.024726),
('2013-02-28', 'man4', 0.005703),
('2013-02-28', 'man6', 0.006496),
('2013-02-28', 'bmark', -0.004624),
('2013-03-31', 'man1', 0.000942),
('2013-03-31', 'man4', 0.007578),
('2013-03-31', 'man6', 0.008333),
('2013-03-31', 'bmark', 0.005031),
('2013-04-30', 'man1', 0.022139),
('2013-04-30', 'man4', 0.007118),
('2013-04-30', 'man6', -0.007312),
('2013-04-30', 'bmark', -0.005431),
('2013-05-31', 'man1', 0.019449),
('2013-05-31', 'man4', 0.005659),
('2013-05-31', 'man6', -0.003902),
('2013-05-31', 'bmark', -0.001878),
('2013-06-30', 'man1', -0.007964),
('2013-06-30', 'man4', 0.003757),
('2013-06-30', 'man6', -0.001899),
('2013-06-30', 'bmark', -0.007060),
('2013-07-31', 'man1', -0.008262),
('2013-07-31', 'man2', -0.002824),
('2013-07-31', 'man4', 0.003762),
('2013-07-31', 'man6', -0.007347),
('2013-07-31', 'bmark', -0.001982),
('2013-08-31', 'man1', 0.009617),
('2013-08-31', 'man2', 0.007319),
('2013-08-31', 'man4', 0.002200),
('2013-08-31', 'man6', -0.002116),
('2013-08-31', 'bmark', 0.003867),
('2013-09-30', 'man1', -0.004118),
('2013-09-30', 'man2', 0.004128),
('2013-09-30', 'man4', 0.001394),
('2013-09-30', 'man6', 0.008333),
('2013-09-30', 'bmark', 0.001356),
('2013-10-31', 'man1', 0.010754),
('2013-10-31', 'man2', -0.001578),
('2013-10-31', 'man4', 0.003483),
('2013-10-31', 'man6', -0.004724),
('2013-10-31', 'bmark', -0.001342),
('2013-11-30', 'man1', 0.002402),
('2013-11-30', 'man2', 0.020835),
('2013-11-30', 'man4', 0.001000),
('2013-11-30', 'man6', 0.003954),
('2013-11-30', 'bmark', -0.000306),
('2013-12-31', 'man1', 0.004581),
('2013-12-31', 'man2', 0.015689),
('2013-12-31', 'man4', 0.002146),
('2013-12-31', 'man6', 0.008333),
('2013-12-31', 'bmark', 0.001118),
('2014-01-31', 'man1', -0.000550),
('2014-01-31', 'man2', -0.001027),
('2014-01-31', 'man3', -0.008245),
('2014-01-31', 'man4', 0.007320),
('2014-01-31', 'man6', 0.004025),
('2014-01-31', 'bmark', -0.007227),
('2014-02-28', 'man1', -0.001512),
('2014-02-28', 'man2', 0.001653),
('2014-02-28', 'man3', -0.009029),
('2014-02-28', 'man4', 0.009919),
('2014-02-28', 'man6', -0.008333),
('2014-02-28', 'bmark', -0.003878),
('2014-03-31', 'man1', 0.008784),
('2014-03-31', 'man2', 0.004364),
('2014-03-31', 'man3', -0.011608),
('2014-03-31', 'man4', 0.001000),
('2014-03-31', 'man6', -0.008333),
('2014-03-31', 'bmark', -0.004822),
('2014-04-30', 'man1', 0.008412),
('2014-04-30', 'man2', -0.012369),
('2014-04-30', 'man3', -0.004692),
('2014-04-30', 'man4', 0.004536),
('2014-04-30', 'man6', -0.006303),
('2014-04-30', 'bmark', 0.004306),
('2014-05-31', 'man1', 0.003945),
('2014-05-31', 'man2', 0.010651),
('2014-05-31', 'man3', -0.016833),
('2014-05-31', 'man4', 0.001000),
('2014-05-31', 'man6', -0.007974),
('2014-05-31', 'bmark', 0.005221),
('2014-06-30', 'man1', 0.012371),
('2014-06-30', 'man2', 0.017730),
('2014-06-30', 'man3', -0.010384),
('2014-06-30', 'man4', 0.010593),
('2014-06-30', 'man6', -0.004781),
('2014-06-30', 'bmark', -0.000731),
('2014-07-31', 'man1', 0.011915),
('2014-07-31', 'man2', 0.004308),
('2014-07-31', 'man3', -0.012965),
('2014-07-31', 'man4', 0.001000),
('2014-07-31', 'man6', 0.007751),
('2014-07-31', 'bmark', -0.009239),
('2014-08-31', 'man1', -0.013738),
('2014-08-31', 'man2', 0.000390),
('2014-08-31', 'man3', 0.000009),
('2014-08-31', 'man4', 0.005139),
('2014-08-31', 'man5', 0.001000),
('2014-08-31', 'man6', -0.003319),
('2014-08-31', 'bmark', -0.003636),
('2014-09-30', 'man1', -0.004081),
('2014-09-30', 'man2', 0.019680),
('2014-09-30', 'man3', -0.008214),
('2014-09-30', 'man4', 0.007976),
('2014-09-30', 'man5', 0.001000),
('2014-09-30', 'man6', -0.003799),
('2014-09-30', 'bmark', 0.005563),
('2014-10-31', 'man1', 0.016080),
('2014-10-31', 'man2', 0.015291),
('2014-10-31', 'man3', -0.002969),
('2014-10-31', 'man4', 0.013030),
('2014-10-31', 'man5', 0.001000),
('2014-10-31', 'man6', -0.004645),
('2014-10-31', 'bmark', 0.001599),
('2014-11-30', 'man1', 0.011241),
('2014-11-30', 'man2', 0.012312),
('2014-11-30', 'man3', 0.007088),
('2014-11-30', 'man4', 0.003840),
('2014-11-30', 'man5', 0.000852),
('2014-11-30', 'man6', 0.006783),
('2014-11-30', 'bmark', -0.008487),
('2014-12-31', 'man1', -0.004251),
('2014-12-31', 'man2', 0.008737),
('2014-12-31', 'man3', -0.013576),
('2014-12-31', 'man4', 0.001000),
('2014-12-31', 'man5', 0.001000),
('2014-12-31', 'man6', -0.003400),
('2014-12-31', 'bmark', 0.001013),
('2015-01-31', 'man1', -0.004039),
('2015-01-31', 'man2', 0.012938),
('2015-01-31', 'man3', -0.011891),
('2015-01-31', 'man4', 0.004576),
('2015-01-31', 'man5', 0.001000),
('2015-01-31', 'man6', -0.004195),
('2015-01-31', 'bmark', -0.002653),
('2015-02-28', 'man1', 0.026326),
('2015-02-28', 'man2', 0.019695),
('2015-02-28', 'man3', -0.013178),
('2015-02-28', 'man4', 0.010737),
('2015-02-28', 'man5', 0.001000),
('2015-02-28', 'man6', 0.006418),
('2015-02-28', 'bmark', -0.009520),
('2015-03-31', 'man1', -0.000628),
('2015-03-31', 'man2', 0.008029),
('2015-03-31', 'man3', 0.005917),
('2015-03-31', 'man4', 0.017461),
('2015-03-31', 'man5', 0.000480),
('2015-03-31', 'man6', -0.005010),
('2015-03-31', 'bmark', 0.007879),
('2015-04-30', 'man1', 0.005688),
('2015-04-30', 'man2', 0.012490),
('2015-04-30', 'man3', -0.018813),
('2015-04-30', 'man4', 0.001000),
('2015-04-30', 'man5', -0.002104),
('2015-04-30', 'man6', 0.003657),
('2015-04-30', 'bmark', 0.009886),
('2015-05-31', 'man1', 0.010039),
('2015-05-31', 'man2', 0.008442),
('2015-05-31', 'man3', -0.014650),
('2015-05-31', 'man4', 0.001000),
('2015-05-31', 'man5', 0.001000),
('2015-05-31', 'man6', 0.001024),
('2015-05-31', 'bmark', 0.001695),
('2015-06-30', 'man1', -0.004267),
('2015-06-30', 'man2', 0.001391),
('2015-06-30', 'man3', -0.022504),
('2015-06-30', 'man4', 0.003104),
('2015-06-30', 'man5', 0.001000),
('2015-06-30', 'man6', -0.003507),
('2015-06-30', 'bmark', 0.009854),
('2015-07-31', 'man1', -0.001672),
('2015-07-31', 'man2', 0.020558),
('2015-07-31', 'man3', -0.011859),
('2015-07-31', 'man4', 0.001000),
('2015-07-31', 'man5', 0.001000),
('2015-07-31', 'bmark', 0.008361),
('2015-08-31', 'man1', 0.015726),
('2015-08-31', 'man2', 0.017419),
('2015-08-31', 'man3', -0.004556),
('2015-08-31', 'man4', 0.001000),
('2015-08-31', 'man5', -0.000462),
('2015-08-31', 'bmark', -0.007253),
('2015-09-30', 'man1', 0.001977),
('2015-09-30', 'man2', 0.007301),
('2015-09-30', 'man3', -0.008110),
('2015-09-30', 'man4', 0.013011),
('2015-09-30', 'man5', -0.001032),
('2015-09-30', 'bmark', 0.009911),
('2015-10-31', 'man1', 0.016201),
('2015-10-31', 'man2', 0.008770),
('2015-10-31', 'man3', -0.005536),
('2015-10-31', 'man4', 0.014527),
('2015-10-31', 'man5', -0.000833),
('2015-10-31', 'bmark', 0.009897),
('2015-11-30', 'man1', 0.019996),
('2015-11-30', 'man2', -0.000785),
('2015-11-30', 'man3', -0.002161),
('2015-11-30', 'man4', 0.001947),
('2015-11-30', 'man5', 0.001000),
('2015-11-30', 'bmark', -0.007307),
('2015-12-31', 'man1', -0.003254),
('2015-12-31', 'man2', 0.009595),
('2015-12-31', 'man3', -0.013785),
('2015-12-31', 'man4', 0.001000),
('2015-12-31', 'man5', 0.001000),
('2015-12-31', 'bmark', 0.007196)
) n (dt, man, R);
SELECT man as manager,
wct.SemiVariance(r) as SemiVariance
FROM #nmanagers m
GROUP BY man;
This produces the following result.
{"columns":[{"field":"manager"},{"field":"SemiVariance","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"manager":"bmark","SemiVariance":"0.00582466662913409"},{"manager":"man1","SemiVariance":"0.00905822245566325"},{"manager":"man2","SemiVariance":"0.00860693161378923"},{"manager":"man3","SemiVariance":"0.00615765424242307"},{"manager":"man4","SemiVariance":"0.00314360617310065"},{"manager":"man5","SemiVariance":"0.00168685674783494"},{"manager":"man6","SemiVariance":"0.0047286238279909"}]}
See Also
SEMIDEVIATION - Calculate the semi-deviation of asset returns